package io.renren.modules.sys.util;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

public class ExcelUtil {
    private static HSSFWorkbook wb = null;
    private static HSSFSheet sheet = null;

	/**
	 *
	 * @param file
	 * @return
	 * @throws IOException
	 */
	public static Workbook readFile(File file) throws IOException {
		FileInputStream in = new FileInputStream(file);
		// 读取excel
		Workbook wb = null;
		POIFSFileSystem fs = null;
		if ("xls".equals(FilesUtil.getFileExtension(file))) {
			// 打开excel
			fs = new POIFSFileSystem(in);
			// 打开workbook
			wb = new HSSFWorkbook(fs);
		} else if ("xlsx".equals(FilesUtil.getFileExtension(file))) {
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 *
	 * @param in
	 * @return
	 * @throws IOException
	 */
	public static Workbook readXLSFile(InputStream in) throws IOException {
		POIFSFileSystem fs = new POIFSFileSystem(in);  // 打开excel
		Workbook wb = new HSSFWorkbook(fs); // 打开workbook
		return wb;
	}


	public static String[] readLine(Sheet st, int rowNum) {
		if (rowNum < 0) {
			return null;
		}
		Row row = st.getRow(rowNum);
		String[] data = new String[row.getLastCellNum()];
		for (int i = 0; i < data.length; i++) {
			data = PoiUtil.toStrings(row, data.length);
		}
		return data;
	}

	public static ArrayList<String[]> readSheetData(Workbook wb, int sheet){
		Sheet st = wb.getSheetAt(0);
		// 获取总行数
		int rowCount = st.getLastRowNum();
		Row title = st.getRow(0);
		int colCount = title.getLastCellNum();
		String[] dummy = new String[colCount];
		Arrays.fill(dummy, "");
		ArrayList<String[]> data = new ArrayList<String[]>(10);
		// 读取数据
		for (int i = 0; i <= rowCount; i++) {
			Row row = st.getRow(i);
			String[] columns = PoiUtil.toStrings(row, colCount);
			if (Arrays.equals(columns, dummy) || columns == null) { // 空行，跳过
				continue;
			}
			data.add(columns);
		}
		return data;
	}

	public static Vector<Vector<String>> readSheetData(String filePath) throws IOException {
        FileInputStream in = new FileInputStream(filePath);
        @SuppressWarnings("resource")
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet  st = wb.getSheetAt(0);
        // 获取总行数
        int rowCount = st.getLastRowNum();
        int colCount = 0;// title.getLastCellNum();
        for (int i = 0; i < rowCount; i++ ) {
            Row title = st.getRow(i);
            if (colCount < title.getLastCellNum()) {
                colCount = title.getLastCellNum();
            }
        }
//            ArrayList<String[]> data = new ArrayList<String[]>(10);
        Vector<Vector<String>> data = new Vector<Vector<String>>();
        // 读取数据
        for (int i = 0; i <= rowCount; i++) {
            Row row = st.getRow(i);
            Vector<String> columns = PoiUtil.toVector(row, colCount);
            if (columns == null) { // 空行，跳过
                continue;
            }
            data.add(columns);
        }
        return data;

    }
	/**
     * Description: <br>
     * 1、处理Excel文件生成<br>
     * Implement: <br>
     * 1、传入写入文件的数据以及输出流，进行文件生成<br>
     *
     * @param data 写入文件数据
     * @param out 输出流
     * @throws IOException
     * @see
     */
	public static void writeFile(ArrayList<String[]> data, OutputStream out) throws IOException {
	    writeFile(data, out,null);
	}

	/**
	 * Description: <br>
	 * 1、处理Excel文件生成<br>
	 * Implement: <br>
	 * 1、传入写入文件的数据以及输出流，进行文件生成<br>
	 *
	 * @param data 写入文件数据
	 * @param out 输出流
	 * @param title sheet名称
	 * @throws IOException
	 * @see
	 */
	public static void writeFile(ArrayList<String[]> data, OutputStream out, String title) throws IOException {
        wb= new HSSFWorkbook();
        // 生成一个表格
        if(StringUtil.isNotEmpty(title)) {
           sheet = wb.createSheet(title);
        }else {
            sheet = wb.createSheet();
        }
        sheet.setDefaultColumnWidth(15);
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(i);
            String[] rowData = data.get(i);
            for (int j = 0; j < rowData.length; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(rowData[j]);
            }
        }
        wb.write(out);
		out.flush();
    }


	/**
	 * list转写入excek的数据
	 * @param list
	 * @param col
	 * @return
	 */
	public static ArrayList<String[]> getData(List list, String[] titel , String[] col ){
		ArrayList<String[]> data = new ArrayList<String[]>();//写入excel的数组
		if (titel!=null){
			data.add(titel);
		}
		for (int i = 0; i < list.size(); i++) {
			Map<String, String> map = null;
			try {
				map = BeanUtils.describe(list.get(i));
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			}
			String tempValue[] = new String[col.length];
			for (int j = 0;j < col.length; j++) {
				String temp = col[j];
				String value = map.get(temp);
				tempValue[j]=value;
			}
			data.add(tempValue);
		}
		return data;
	}

}
